﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BacklogParser
{
    public static class StringExtenstions
    {
        public static string EscapeForSql(this string str)
        {
            if (string.IsNullOrEmpty(str))
                return "";
            var res = str.Replace("'", "''").Replace("--", "-");

            while (res.Contains("--"))
                res = res.Replace("--", "-");

            return res;
        }
    }
    public class SqlGenerator
    {
        private RegisterDocument document;
        private StringBuilder builder;

        public SqlGenerator()
        {
            this.document = new RegisterDocument();
            this.builder = new StringBuilder();
        }

        public StringBuilder GenerateSqlForDocument(RegisterDocument document)
        {
            this.document = document;

            this.Print();

            


            return builder;
        }


        private void Print()
        {

            this.PrintDocument();
            this.PrintAgents();

            this.PrintApplicants();
            this.PrintInventors();
            this.PrintPriorityClaims();
            this.PrintIpcr();
            this.PrintInventionTitles();
            this.PrintApplicationReferences();
            this.PrintPublicationReferences();
            this.DesignationOfStates();
            this.Licensees();
            this.LapsedInCountry();
            this.Citations();
            this.DateRightsEffective();
            this.BioDeposit();
            this.OppositionData();
            this.AppealData();
            this.PetitionReview();
            this.RevocationProcedure();
            this.LimitationProcedure();
            this.Misc();
            this.RelatedDoc();

            this.SearchReport();
            this.SearchReportPublication();
            builder.AppendLine();
            this.ProceduralData();
            builder.AppendLine();
            this.DossierEvents();
            this.DossierText();

        }

        private void DossierText()
        {
            var text = this.document.EventData.SelectMany(x => x.DossierText).ToList();
            if (text.Count == 0)
                return;

            builder.Append("insert into dossier_text (");
            builder.Append("doc_id,");
            builder.Append("d_index,");
            builder.Append("text_type,");
            builder.Append("d_text");

            builder.Append(") values ");

            var helper = new StringBuilder();
            foreach (var item in text)
            {

                var values = new[]
                {
                    this.document.DocumentId,
                    item.d_index,
                    item.text_type.EscapeForSql(),
                    item.text.EscapeForSql(),
                };

                helper.Append("('" + string.Join("', '", values) + "'),");
            }
            builder.Append(helper.ToString().TrimEnd(new[] { ',' }));
            builder.AppendLine(";");

        }

        private void DossierEvents()
        {
            if (this.document.EventData.Count == 0)
                return;

            builder.Append("insert into dossier_events (");
            builder.Append("doc_id,");
            builder.Append("d_index,");          

            builder.Append("event_date,");
            builder.Append("event_code,");
            builder.Append("gazette_num,");
            builder.Append("gazette_date,");            
            builder.Append("event_type");
            builder.Append(") values ");

            var helper = new StringBuilder();
            foreach (var item in this.document.EventData)
            {
                
                var values = new[]
                {
                    this.document.DocumentId,
                    item.d_index.EscapeForSql(),
                    item.event_date.EscapeForSql(),
                    item.event_code.EscapeForSql(),
                    item.gazette_num.EscapeForSql(),
                    item.gazette_date,
                    item.event_type.EscapeForSql(),
                };

                helper.Append("('" + string.Join("', '", values) + "'),");
            }
            builder.Append(helper.ToString().TrimEnd(new[] { ',' }));
            builder.AppendLine(";");


        }

        private void ProceduralData()
        {
            builder.Append("insert into procedural_data (");
            builder.Append("doc_id,");
            builder.Append("doc_index,");
            builder.Append("step_code,");
            builder.Append("time_limit,");
            builder.Append("affected_states,");
            builder.Append("gazette_date,");
            builder.Append("gazette_num,");
            builder.Append("gazette_text");

            builder.Append(") values ('");
            
            var helper = new StringBuilder();
            foreach (var item in this.document.ProceduralData)
            {
                item.Index = this.document.ProceduralData.IndexOf(item);
                var values = new[]
                {
                    this.document.DocumentId,
                    item.Index.ToString(),
                    item.step_code,
                    item.time_limit,
                    item.affected_states,
                    item.gazzete_date,
                    item.gazzete_num,
                    item.gazzete_text
                };

                helper.Append(string.Join("', '", values));
            }
            builder.Append(helper.ToString().TrimEnd(new[] { ',' }));
            builder.AppendLine("');");

            this.StepResults();
            this.StepText();
            this.StepDate();
        }

        private void StepDate()
        {
            foreach (var item in this.document.ProceduralData)
            {
                foreach (var stepResult in item.step_date)
                {
                    builder.Append("insert into step_date (");
                    builder.Append("doc_id,");
                    builder.Append("doc_index,");
                    builder.Append("step_date,");
                    builder.Append("date_type");
                    builder.Append(") values ('");

                    var helper = new StringBuilder();

                    var values = new string[]
                    {
                        this.document.DocumentId,
                        item.Index.ToString(),
                        stepResult.date,
                        stepResult.date_type
                    };

                    helper.Append(string.Join("', '", values));
                    builder.Append(helper.ToString().TrimEnd(new[] { ',' }));
                    builder.AppendLine("');");
                }
            }
        }

        private void StepText()
        {
            foreach (var item in this.document.ProceduralData)
            {
                foreach (var stepResult in item.step_text)
                {
                    builder.Append("insert into step_text (");
                    builder.Append("doc_id,");
                    builder.Append("doc_index,");
                    builder.Append("text_type,");
                    builder.Append("step_text");
                    builder.Append(") values ('");

                    var helper = new StringBuilder();

                    var values = new string[]
                    {
                        this.document.DocumentId,
                        item.Index.ToString(),
                        stepResult.text_type,
                        stepResult.step_text
                    };

                    helper.Append(string.Join("', '", values));
                    builder.Append(helper.ToString().TrimEnd(new[] { ',' }));
                    builder.AppendLine("');");
                }
            }
        }

        private void StepResults()
        {
            foreach (var item in this.document.ProceduralData)
            {
                foreach (var stepResult in item.step_result)
                {
                    builder.Append("insert into step_result (");
                    builder.Append("doc_id,");
                    builder.Append("doc_index,");
                    builder.Append("step_result,");
                    builder.Append("result_type");
                    builder.Append(") values ('");

                    var helper = new StringBuilder();

                    var values = new string[]
                    {
                        this.document.DocumentId,
                        item.Index.ToString(),
                        stepResult.step_result,
                        stepResult.result_type
                    };

                    helper.Append(string.Join("', '", values));
                    builder.Append(helper.ToString().TrimEnd(new[] { ',' }));
                    builder.AppendLine("');");
                }
            }
        }

        private void PrintDocument()
        {

            // TODO: Make the names to be references
            builder.Append("insert into documents (");
            builder.Append("doc_id,");
            builder.Append("doc_num,");
            builder.Append("publn_date,");
            builder.Append("publn_kind,");
            builder.Append("publn_country,");
            builder.Append("language_of_filing,");
            builder.Append("appl_refused,");
            builder.Append("appl_reestablished,");
            builder.Append("appl_wirthdrawn,");
            builder.Append("revocation_date,");
            builder.Append("deemed_wirthdraw");

            //

            builder.Append(") values ('");

            var values = new[]
            {
                this.document.DocumentId,
                this.document.DocumentNumber,
                this.document.Publn_Date,
                this.document.Publn_Kind,
                this.document.Publn_Country,
                this.document.LanguageOfFiling,
                this.document.DateApplicationRefused,
                this.document.DateApplicationReestablished,
                this.document.DateApplicationDeemedWithdrawn,
                this.document.DateOfRevocation,
                this.document.DateApplicationDeemedWithdrawn
            };

            builder.Append(string.Join("', '", values));

            builder.Append("');");
        }
        private void PrintAgents()
        {
            if (this.document.Agents.Count == 0)
                return;

            // TODO: Make the names to be references
            builder.Append("insert into agents (");
            builder.Append("doc_id,");
            builder.Append("cdsid,");
            builder.Append("name,");
            builder.Append("addr1,");
            builder.Append("addr2,");
            builder.Append("addr3,");
            builder.Append("addr4,");
            builder.Append("addr5,");
            builder.Append("cntry,");

            builder.Append("change_date,");
            builder.Append("change_gazette,");

            builder.Append("rep_type,");
            builder.Append("a_sequence");





            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.Agents)
            {
                helper.Append("('");
                
                var values = new[]
                {
                    this.document.DocumentId.EscapeForSql(),
                    item.CdsId,
                    item.Name.EscapeForSql(),
                    item.Address1.EscapeForSql(),
                    item.Address2.EscapeForSql(),
                    item.Address3.EscapeForSql(),
                    item.Address4.EscapeForSql(),
                    item.Address5.EscapeForSql(),

                    item.Country.EscapeForSql(),

                    item.ChangeDate.EscapeForSql(),
                    item.ChangeGazetteNum.EscapeForSql(),
                    item.RepType.EscapeForSql(),
                    item.Sequence

                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintApplicants()
        {
            if (this.document.Applicants.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into applicants (");
            builder.Append("doc_id,");
            builder.Append("cdsid,");
            builder.Append("name,");
            builder.Append("addr1,");
            builder.Append("addr2,");
            builder.Append("addr3,");
            builder.Append("addr4,");
            builder.Append("addr5,");
            builder.Append("cntry,");

            builder.Append("change_date,");
            builder.Append("change_gazette,");

            builder.Append("a_sequence,");
            builder.Append("app_type,");
            builder.Append("designation");



            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.Applicants)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.CdsId,
                    item.Name.EscapeForSql(),
                    item.Address1.EscapeForSql(),
                    item.Address2.EscapeForSql(),
                    item.Address3.EscapeForSql(),
                    item.Address4.EscapeForSql(),
                    item.Address5.EscapeForSql(),

                    item.Country.EscapeForSql(),
                    item.ChangeDate,
                    item.ChangeGazetteNum.EscapeForSql(),
                    item.Sequence,
                    item.AppType.EscapeForSql(),
                    item.Designation.EscapeForSql()
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintInventors()
        {
            if (this.document.Inventors.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into inventors (");
            builder.Append("doc_id,");
            builder.Append("cdsid,");

            builder.Append("name,");
            builder.Append("addr1,");
            builder.Append("addr2,");
            builder.Append("addr3,");
            builder.Append("addr4,");
            builder.Append("addr5,");

            builder.Append("cntry,");

            builder.Append("change_date,");
            builder.Append("change_gazette,");
            builder.Append("a_sequence");



            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.Inventors)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.CdsId,
                    item.Name.EscapeForSql(),
                    item.Address1.EscapeForSql(),
                    item.Address2.EscapeForSql(),
                    item.Address3.EscapeForSql(),
                    item.Address4.EscapeForSql(),
                    item.Address5.EscapeForSql(),

                    item.Country.EscapeForSql(),
                    item.ChangeDate.EscapeForSql(),
                    item.ChangeGazette.EscapeForSql(),
                    item.Sequence

                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintPriorityClaims()
        {
            if (this.document.PriorityClaims.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into priority_claims (");
            builder.Append("doc_id,");
            builder.Append("doc_num,");
            builder.Append("kind,");
            builder.Append("sequence,");
            builder.Append("date,");
            builder.Append("cntry");


            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.PriorityClaims)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.DocNumber.EscapeForSql(),
                    item.Kind.EscapeForSql(),
                    item.Sequence.EscapeForSql(),
                    item.Date,
                    item.Country
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintIpcr()
        {
            if (this.document.ClassificationsIpcr.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into classification_ipcr (");
            builder.Append("doc_id,");
            builder.Append("doc_num,");
            builder.Append("text");


            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.ClassificationsIpcr)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.Id.EscapeForSql(),
                    item.Text.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintInventionTitles()
        {
            if (this.document.InventionTitles.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into invention_titles (");
            builder.Append("doc_id,");
            builder.Append("lang,");
            builder.Append("title");


            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.InventionTitles)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.Language.EscapeForSql(),
                    item.Title.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintApplicationReferences()
        {
            if (this.document.ApplicationReferences.DocumentIds.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into application_references (");
            builder.Append("doc_id,");
            builder.Append("doc_num,");
            builder.Append("cntry,");
            builder.Append("date,");
            builder.Append("kind,");
            builder.Append("lang");


            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.ApplicationReferences.DocumentIds)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.DocNumber.EscapeForSql(),
                    item.Country.EscapeForSql(),
                    item.Date.EscapeForSql(),
                    item.Kind.EscapeForSql(),
                    item.Lang.EscapeForSql()
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PrintPublicationReferences()
        {
            if (this.document.PublicationReferences.DocumentIds.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into publication_references (");
            builder.Append("doc_id,");
            builder.Append("doc_num,");
            builder.Append("cntry,");
            builder.Append("date,");
            builder.Append("kind,");
            builder.Append("lang");


            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.PublicationReferences.DocumentIds)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.DocNumber.EscapeForSql(),
                    item.Country.EscapeForSql(),
                    item.Date.EscapeForSql(),
                    item.Kind.EscapeForSql(),
                    item.Lang.EscapeForSql()
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void DesignationOfStates()
        {
            if (this.document.DesignationOfStates.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into designation_of_states (");
            builder.Append("doc_id,");
            builder.Append("region,");
            builder.Append("icerpat");

            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.DesignationOfStates)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.Region.EscapeForSql(),
                    item.ICIREPATCountries.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void Licensees()
        {
            if (this.document.Licences.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into designation_of_states (");
            builder.Append("doc_id,");
            builder.Append("name,");
            builder.Append("address,");
            builder.Append("cntry,");
            builder.Append("date,");
            builder.Append("effective_in,");
            builder.Append("designation,");
            builder.Append("sequence,");
            builder.Append("type_licensee");

            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.Licences.SelectMany(x => x.Licensees))
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.Name.EscapeForSql(),
                    item.Address.EscapeForSql(),
                    item.Country.EscapeForSql(),
                    item.Date.EscapeForSql(),
                    item.EffectiveIn.EscapeForSql(),
                    item.Designation.EscapeForSql(),
                    item.Sequence.EscapeForSql(),
                    item.TypeLicense.EscapeForSql()
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void LapsedInCountry()
        {
            if (this.document.TermsOfGrant.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into lapsed_in_country (");
            builder.Append("doc_id,");
            builder.Append("cntry,");
            builder.Append("date");

            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.TermsOfGrant)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.Country.EscapeForSql(),
                    item.Date.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void Citations()
        {
            var citations = this.document.CitationReferences.SelectMany(x => x.Citations).ToList();
            if (citations.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into lapsed_in_country (");
            builder.Append("doc_id,");
            builder.Append("doc_num,");
            builder.Append("office,");
            builder.Append("citedin,");
            builder.Append("doi,");
            builder.Append("category,");
            builder.Append("npcit,");
            builder.Append("doc_cntry");


            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in citations)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.DocumentNumber.EscapeForSql(),
                    item.Office.EscapeForSql(),
                    item.CitedIn.EscapeForSql(),
                    item.Doi.EscapeForSql(),
                    item.Category.EscapeForSql(),
                    item.Npcit.EscapeForSql(),
                    item.DocumentCountry.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void DateRightsEffective()
        {
            // TODO: Make the names to be references


            var helper = new StringBuilder();

            helper.Append("('");
            var item = this.document.DatesRightsEffective;
            var values = new[]
            {
                    this.document.DocumentId,
                    item.FirstExaminationReportDespatched.EscapeForSql(),
                    item.PatentMaintainedAsAmended.EscapeForSql(),
                    item.ProceedingsResumed.EscapeForSql(),
                    item.ProceedingsInterrupted.EscapeForSql(),
                    item.ProceedingsSuspended.EscapeForSql(),
                    item.RequestForConversionToNationalApplication_Date.EscapeForSql(),
                    item.RequestForConversionToNationalApplication_Country.EscapeForSql(),
                    item.RequestForExamination.EscapeForSql(),
                };

            var res = string.Join("", values.Skip(1));

            if (string.IsNullOrWhiteSpace(res))
                return;

            helper.Append(string.Join("', '", values));
            helper.Append("'),");

            builder.Append("insert into datesrights_effective (");
            builder.Append("doc_id,");
            builder.Append("f_examin_rep_dispatch,");
            builder.Append("maintained_amended,");
            builder.Append("p_resumed,");
            builder.Append("p_interrupted,");
            builder.Append("p_suspended,");
            builder.Append("req_conversiontonational_date,");
            builder.Append("req_conversiontonational_cntry,");
            builder.Append("req_examination");

            builder.Append(") values ");

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void BioDeposit()
        {
            if (this.document.BioDeposit.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into bio_deposit (");
            builder.Append("doc_id,");
            builder.Append("address,");
            builder.Append("bio_accno,");
            builder.Append("dtext,");

            builder.Append(") values ");

            var helper = new StringBuilder();

            foreach (var item in this.document.BioDeposit)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.Address.EscapeForSql(),
                    item.BioAccno.EscapeForSql(),
                    item.Dtext.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }

            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void OppositionData()
        {
            var helper = new StringBuilder();
            var helper2 = new StringBuilder();

            if (this.document.OppositionData.Count == 0)
                return;
            // TODO: Make the names to be references
            builder.Append("insert into oppositiondata (");
            builder.Append("doc_id,");
            builder.Append("o_notfiled,");
            builder.Append("o_terminated,");
            builder.Append("o_rejected");

            builder.AppendLine(") values ");


            foreach (var item in this.document.OppositionData)
            {
                helper.Append("('");

                var values = new[]
                {
                    this.document.DocumentId,
                    item.OpositionNotFilled.EscapeForSql(),
                    item.DateTerminated.EscapeForSql(),
                    item.DateRejected.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");


            }

            builder.AppendLine(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");


            var opponents = this.document.OppositionData.SelectMany(x => x.Opponent).ToList();

            if (opponents.Count > 0)
            {
                helper2.Append("insert into opponent (");
                helper2.Append("doc_id,");
                helper2.Append("cdsid,");
                helper2.Append("sequence,");
                helper2.Append("opp_name,");
                helper2.Append("opp_addr1,");
                helper2.Append("opp_addr2,");
                helper2.Append("opp_addr3,");
                helper2.Append("opp_addr4,");
                helper2.Append("opp_addr5,");
                helper2.Append("agent_name,");
                helper2.Append("agent_addr1,");
                helper2.Append("agent_addr2,");
                helper2.Append("agent_addr3,");
                helper2.Append("agent_addr4,");
                helper2.Append("agent_addr5,");
                helper2.Append("agent_cntry,");
                helper2.Append("status,");
                helper2.Append("opposition_filed");

                helper2.AppendLine(") values ");

                foreach (var item in opponents)
                {
                    helper2.Append("('");

                    var values2 = new[]
                    {
                            this.document.DocumentId,
                            item.CdsId,
                            item.Sequence.EscapeForSql(),

                            item.Name.EscapeForSql(),
                            item.Address1.EscapeForSql(),
                            item.Address2.EscapeForSql(),
                            item.Address3.EscapeForSql(),
                            item.Address4.EscapeForSql(),
                            item.Address5.EscapeForSql(),

                            item.Agent.Name.EscapeForSql(),
                            item.Agent.Address1.EscapeForSql(),
                            item.Agent.Address2.EscapeForSql(),
                            item.Agent.Address3.EscapeForSql(),
                            item.Agent.Address4.EscapeForSql(),
                            item.Agent.Address5.EscapeForSql(),

                            item.Agent.Country.EscapeForSql(),
                            item.OpponentStatus.EscapeForSql(),
                            item.DateOppositionFiled.EscapeForSql()
                        };

                    helper2.Append(string.Join("', '", values2));
                    helper2.Append("'),");
                }

                builder.Append(helper2.ToString().TrimEnd(new[] { ',' }));
                builder.AppendLine(";");

            }



        }
        private void AppealData()
        {
            // TODO: Make the names to be references
            var appealData = this.document.OfficeSpecificBibData.SelectMany(x => x.AppealData).ToList();

            if (appealData.Count == 0) return;

            builder.Append("insert into appeal_data (");
            builder.Append("doc_id,");
            builder.Append("date,");
            builder.Append("grounds,");
            builder.Append("inter_rev,");
            builder.Append("appeal_res");

            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in appealData)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.Date.EscapeForSql(),
                    item.DateStatementsOfGroundsFiled.EscapeForSql(),
                    item.DateInterlocutoryRevision.EscapeForSql(),
                    item.AppealResult.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void PetitionReview()
        {
            // TODO: Make the names to be references
            var petitions = this.document.OfficeSpecificBibData.SelectMany(x => x.PetitionReview).ToList();

            if (petitions.Count == 0) return;

            builder.Append("insert into petition_review (");
            builder.Append("doc_id,");
            builder.Append("appeal_num,");
            builder.Append("date,");
            builder.Append("code,");
            builder.Append("decision_date,");
            builder.Append("kind");

            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in petitions)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.ReviewAppealNumber.EscapeForSql(),
                    item.ReviewDate.EscapeForSql(),
                    item.ReviewPetitionerCode.EscapeForSql(),
                    item.ReviewDecisionDate.EscapeForSql(),
                    item.ReviewKind.EscapeForSql()
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void RevocationProcedure()
        {
            // TODO: Make the names to be references
            var petitions = this.document.OfficeSpecificBibData.SelectMany(x => x.RevocationProcedure).ToList();

            if (petitions.Count == 0) return;

            builder.Append("insert into revocation_proc (");
            builder.Append("doc_id,");
            builder.Append("proc_date,");
            builder.Append("proc_decision,");
            builder.Append("proc_paid,");
            builder.Append("date_dispatch,");
            builder.Append("legal_effect");

            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in petitions)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.ProcedureDate.EscapeForSql(),
                    item.ProcedureDecision.EscapeForSql(),
                    item.ProcedurePaid.EscapeForSql(),
                    item.DateDispatch.EscapeForSql(),
                    item.LegalEffect.EscapeForSql()
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void LimitationProcedure()
        {
            // TODO: Make the names to be references
            var petitions = this.document.OfficeSpecificBibData.SelectMany(x => x.LimitationProcedure).ToList();

            if (petitions.Count == 0) return;

            builder.Append("insert into revocation_proc (");
            builder.Append("doc_id,");
            builder.Append("filing_date,");
            builder.Append("filing_decision,");
            builder.Append("rejection_kind,");
            builder.Append("reject_dispatch,");
            builder.Append("reject_legal_date,");

            builder.Append("allowed_dispatch_date,");
            builder.Append("allowed_request_pay_date");

            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in petitions)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.FilingDate.EscapeForSql(),
                    item.FilingDecision.EscapeForSql(),
                    item.RejectionKind.EscapeForSql(),
                    item.RejectionDispatchDate.EscapeForSql(),
                    item.RejectionLegalDate.EscapeForSql(),

                    item.AllowedDispatchDate.EscapeForSql(),
                    item.AllowedRequestPayDate.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void Misc()
        {
            // TODO: Make the names to be references
            var petitions = this.document.Miscellaneous.ToList();

            if (petitions.Count == 0) return;

            builder.Append("insert into misc (");
            builder.Append("doc_id,");
            builder.Append("lang,");
            builder.Append("text");

            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in petitions)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.Lang.EscapeForSql(),
                    item.Text.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
        private void RelatedDoc()
        {
            // TODO: Make the names to be references
            if (!string.IsNullOrWhiteSpace(this.document.RelatedDocument.GetDate()))
            {
                builder.Append("insert into related_doc (");
                builder.Append("doc_id,");
                builder.Append("continuation,");
                builder.Append("prev_filed_app");

                builder.Append(") values ");

                var relatedDoc = this.document.RelatedDocument;

                var helper = new StringBuilder();

                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    relatedDoc.Continuation.EscapeForSql(),
                    relatedDoc.PreviouslyFiledApplication.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");

                builder.Append(helper.ToString().Trim(new[] { ',' }));
                builder.AppendLine(";");

            }
            ////----------
            if (!string.IsNullOrWhiteSpace(document.RelatedDocument.GetChildData()))
            {
                builder.Append("insert into child_doc (");
                builder.Append("doc_id,");
                builder.Append("status,");
                builder.Append("grant_doc,");
                builder.Append("pct_doc,");

                builder.Append("cntry,");

                builder.Append("doc_num,");
                builder.Append("date,");
                builder.Append("kind,");
                builder.Append("lang");
                builder.Append(") values ");

                var childDoc = this.document.RelatedDocument.Relation.ChildDoc;

                var helperChild = new StringBuilder();

                helperChild.Append("('");
                var valuesChild = new[]
                {
                    this.document.DocumentId,
                    childDoc.ChildStatus.EscapeForSql(),
                    childDoc.ChildGrantDocument.EscapeForSql(),
                    childDoc.ChildPctDocument.EscapeForSql(),

                    childDoc.DocumentIds.Country.EscapeForSql(),
                    childDoc.DocumentIds.DocNumber.EscapeForSql(),
                    childDoc.DocumentIds.Date.EscapeForSql(),
                    childDoc.DocumentIds.Kind.EscapeForSql(),
                    childDoc.DocumentIds.Lang.EscapeForSql(),
               };

                helperChild.Append(string.Join("', '", valuesChild));
                helperChild.Append("'),");

                builder.Append(helperChild.ToString().TrimEnd(new[] { ',' }));
                builder.AppendLine(";");

            }

            // ---------------------------------
            if (!string.IsNullOrWhiteSpace(document.RelatedDocument.GetParentData()))
            {
                builder.Append("insert into parent_doc (");
                builder.Append("doc_id,");
                builder.Append("status,");
                builder.Append("grant_doc,");
                builder.Append("pct_doc,");
                builder.Append("cntry,");

                builder.Append("doc_num,");
                builder.Append("date,");
                builder.Append("kind,");
                builder.Append("lang");
                builder.Append(") values ");

                var parentDoc = this.document.RelatedDocument.Relation.ParentDoc;

                var helperParent = new StringBuilder();

                helperParent.Append("('");
                var valuesParent = new[]
                {
                    this.document.DocumentId,
                    parentDoc.ParentStatus.EscapeForSql(),
                    parentDoc.ParentGrantDocument.EscapeForSql(),
                    parentDoc.ParentPctDocument.EscapeForSql(),

                    parentDoc.DocumentIds.Country.EscapeForSql(),
                    parentDoc.DocumentIds.DocNumber.EscapeForSql(),
                    parentDoc.DocumentIds.Date.EscapeForSql(),
                    parentDoc.DocumentIds.Kind.EscapeForSql(),
                    parentDoc.DocumentIds.Lang.EscapeForSql(),


                };

                helperParent.Append(string.Join("', '", valuesParent));
                helperParent.Append("'),");



                builder.Append(helperParent.ToString().Trim(new[] { ',' }));
                builder.AppendLine(";");

            }
        }
        private void SearchReport()
        {
            // TODO: Make the names to be references
            var petitions = this.document.SearchReport.ToList();

            if (petitions.Count == 0) return;

            builder.Append("insert into search_report (");
            builder.Append("doc_id,");
            builder.Append("date_mailed,");
            builder.Append("declaration_nosearch,");

            builder.Append("id,");
            builder.Append("office,");
            builder.Append("search_type");


            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in petitions)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.DateMailed.EscapeForSql(),
                    item.DeclarationOfNoSearch.EscapeForSql(),

                    item.Id.EscapeForSql(),
                    item.Office.EscapeForSql(),
                    item.SearchType.EscapeForSql(),

                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }


        private void SearchReportPublication()
        {
            // TODO: Make the names to be references
            var petitions = this.document.SearchReport.SelectMany(x => x.SearchReportPublication).ToList();

            if (petitions.Count == 0) return;

            builder.Append("insert into search_report_publication (");
            builder.Append("doc_id,");
            builder.Append("cntry,");
            builder.Append("doc_num,");
            builder.Append("date,");
            builder.Append("kind,");
            builder.Append("lang");


            builder.Append(") values ");


            var helper = new StringBuilder();
            foreach (var item in petitions)
            {
                helper.Append("('");
                var values = new[]
                {
                    this.document.DocumentId,
                    item.Country.EscapeForSql(),
                    item.DocNumber.EscapeForSql(),

                    item.Date.EscapeForSql(),
                    item.Kind.EscapeForSql(),
                    item.Lang.EscapeForSql(),
                };

                helper.Append(string.Join("', '", values));
                helper.Append("'),");
            }
            builder.Append(helper.ToString().Trim(new[] { ',' }));
            builder.AppendLine(";");

        }
    }
}


